package cn.com.headfree.workflowant.dao.impl;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Repository;

import com.gta.edu.sdk.base.dao.impl.BaseDaoImpl;
import com.gta.edu.sdk.common.dto.Page;

import cn.com.headfree.entity.TBusinessWorkflowBind;
import cn.com.headfree.entity.TMouldInfo;
import cn.com.headfree.entity.TOrgDept;
import cn.com.headfree.entity.TSysDictionaryInfo;
import cn.com.headfree.entity.isc.User;
import cn.com.headfree.util.DateUtils;
import cn.com.headfree.vo.workflow.ProcessListVo;
import cn.com.headfree.workflowant.dao.IWorkFlowAntDao;
import cn.com.headfree.workflowant.dto.ApproveWorkFlowDto;
import cn.com.headfree.workflowant.dto.BusinessWorkFlowDto;
import cn.com.headfree.workflowant.dto.WorkFlowCount;
import io.micrometer.core.instrument.util.StringUtils;

/**  
 * @Title  WorkFlowAntDaoImpl.java   
 * @Package cn.com.headfree.workflowant.dao.impl   
 * @Description 流程daoImpl
 * @author yangwang 
 * @date  2020年7月29日 下午5:38:27   
 */
@Repository("workFlowAntDao")
public class WorkFlowAntDaoImpl extends BaseDaoImpl<ApproveWorkFlowDto> implements IWorkFlowAntDao {
	
	/**
	 * 获取某个单位下的所有单位
	 * @author yangwang
	 * @return  List<TOrgDept>
	 *  */
	@Override
	public List<TOrgDept> getWorkFlowUnits(){
		String sql = "select distinct id,name,p_id";
		StringBuilder sb = new StringBuilder(sql);
		
		sb.append(" from t_org_dept")
		.append(" where is_audit_unit=0")
		.append(" order by sort asc");
		
		return this.findAllBySQL(TOrgDept.class, sb.toString(), null, false, new Object[] {});
	}

	/**
	 * 获取所有业务类型
	 * @author yangwang
	 * @param ids
	 * @return  List<TMouldInfo>
	 *  */
	@Override
	public List<TMouldInfo> getMouldInfoList(String ids){
		String sql = "select distinct ID,MOULD_NAME,TYPE,VALUE,SORT,EASY_NAME,TAB_NAME";
		StringBuilder sb = new StringBuilder(sql);
		
		sb.append(" from t_mould_info")
		.append(" where DEL_SIGN=0")
		.append(" and EASY_NAME <> 'jbxx'")
		.append(" and TAB_NAME is not null");
		
		List<Object> paramList = new ArrayList<Object>();
		if(StringUtils.isNotBlank(ids)) {
			String[] idArr = ids.split(",|;|，|；");
			if((null != idArr)&&(0 < idArr.length)) {
				sb.append(" and (");
				Boolean bool = false;
				for(int i=0;i<idArr.length;i++) {
					if(StringUtils.isNotBlank(idArr[i])) {
						if(bool) {
							sb.append(" or ");
						}
						sb.append(" id=?");
						bool = true;
						paramList.add(idArr[i]);
					}
				}
				sb.append(")");
			}
		}
		
		sb.append(" order by sort asc");
		
		return this.findAllBySQL(TMouldInfo.class, sb.toString(), null, false, paramList.toArray());
	}
	
	/**
	 * 根据id获取业务类型
	 * @author yangwang
	 * @param id
	 * @return  TMouldInfo
	 *  */
	@Override
	public TMouldInfo getMouldInfoById(String id) {
		String sql = "select distinct ID,MOULD_NAME,TYPE,VALUE,SORT,EASY_NAME,TAB_NAME";
		StringBuilder sb = new StringBuilder(sql);
		
		sb.append(" from t_mould_info")
		.append(" where DEL_SIGN=0")
		.append(" and EASY_NAME <> 'jbxx'")
		.append(" and id=?");
		
		return this.findUnique(TMouldInfo.class, sb.toString(), new Object[] {id});
	}

	/**
	 * 根据单位和流程获取流程绑定信息
	 * @author yangwang
	 * @param orgId 单位id
	 * @param flowId 流程id
	 * @return  TBusinessWorkflowBind
	 *  */
	@Override
	public List<TBusinessWorkflowBind> getWorkflowBindByOrgAndFlowId(String orgId,String flowId) {
		String sql = "select distinct id,org_id,business_type_id,flow_id"; 
		StringBuilder sb = new StringBuilder(sql);
		
		sb.append(" from t_business_workflow_bind")
		.append(" where org_id=?")
		.append(" and flow_id=?");
		
		return this.findAllBySQL(TBusinessWorkflowBind.class, sb.toString(), null, false, new Object[] {orgId,flowId});
	}
	
	/**
	 * 获取流程绑定
	 * @author yangwang
	 * @param bind 绑定信息
	 * @return TBusinessWorkflowBind
	 *  */
	@Override
	public TBusinessWorkflowBind getWorkFlowBind(TBusinessWorkflowBind bind) {
		String sql = "select distinct id,org_id,business_type_id,flow_id"; 
		StringBuilder sb = new StringBuilder(sql);
		
		sb.append(" from t_business_workflow_bind")
		.append(" where org_id=?")
		.append(" and business_type_id=?");
		
		return this.findUnique(TBusinessWorkflowBind.class, sb.toString(), new Object[] {bind.getOrgId(),bind.getBusinessTypeId()});
	}
	
	/**
	 * 插入流程绑定
	 * @author yangwang
	 * @param bind 绑定信息
	 *  */
	@Override
	public void insertWorkFlowBind(TBusinessWorkflowBind bind) {
		String sql = "insert into t_business_workflow_bind(id,org_id,business_type_id,flow_id) values"; 
		StringBuilder sb = new StringBuilder(sql);
		sb.append("(#{id,jdbcType=VARCHAR},")
		.append("#{org_id,jdbcType=VARCHAR},")
		.append("#{business_type_id,jdbcType=VARCHAR},")
		.append("#{flow_id,jdbcType=VARCHAR})");
		
		Map<String, Object> params = new HashMap<String, Object>();
        params.put("id", bind.getId());
        params.put("org_id", bind.getOrgId());
        params.put("business_type_id", bind.getBusinessTypeId());
        params.put("flow_id", bind.getFlowId());
        
        this.executeSql(sb.toString(), params);
	}
	
	/**
	 * 更新流程绑定
	 * @author yangwang
	 * @param bind 绑定信息
	 *  */
	@Override
	public void updateWorkFlowBind(TBusinessWorkflowBind bind) {
		String sql = "UPDATE t_business_workflow_bind SET "; 
		StringBuilder sb = new StringBuilder(sql);
		
		sb.append(" org_id=#{org_id,jdbcType=VARCHAR},")
		.append(" business_type_id=#{business_type_id,jdbcType=VARCHAR},")
		.append(" flow_id=#{flow_id,jdbcType=VARCHAR}");
		sb.append(" where id=#{id,jdbcType=VARCHAR} ");
		
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("org_id", bind.getOrgId());
		params.put("business_type_id", bind.getBusinessTypeId());
		params.put("flow_id", bind.getFlowId());
		params.put("id", bind.getId());
		
		this.executeSql(sb.toString(), params);
	}
	
	/**
	 * 根据erpno获取部门信息
	 * @author yangwang
	 * @param erpNo erp编码
	 * @param List<TOrgDept>
	 *  */
	@Override
	public List<TOrgDept> getDeptByErpNo(String erpNo){
		String sql = "select tod.id,tod.name";
		StringBuilder sb = new StringBuilder(sql);
		sb.append(" from t_dept_emp_rela tder")
		.append(" left join t_org_dept tod on tder.dept_id=tod.id")
		.append(" where tder.erp_no=?");
		
		return this.findAllBySQL(TOrgDept.class, sb.toString(), null, false, new Object[] {erpNo});
	}
	
	/**
	 * 根据表名和业务id获取业务相关信息
	 * @author yangwang
	 * @param tableName 表名
	 * @param businessId 业务id
	 * @param ApproveWorkFlowDto
	 *  */
	@Override
	public ApproveWorkFlowDto getBusinessDataByParams(String tableName,String businessId) {
		String sql = "select distinct ID as BUSINESS_ID,CREATE_TIME as DEAL_TIME,creater_id as erp_no,creater_name as user_name,update_time as HANDLE_TIME,flow_status,primitive_id,orignal_id";
		StringBuilder sb = new StringBuilder(sql);
		
		sb.append(" from ")
		.append(tableName)
		.append(" where 1=1")
//		.append(" and DEL_SIGN=0")
		.append(" and ID=?");
		
		return this.findUnique(ApproveWorkFlowDto.class, sb.toString(), new Object[] {businessId});
	}
	
	/**
	 * 获取审核进度列表数据
	 * @author yangwang
	 * @param page 分页
	 * @param process 进度数据
	 * @param erpNo erp编码
	 *  */
	@Override
	public void approveProcessPage(Page<ProcessListVo> page,ProcessListVo process) {
		String sql = "select distinct t.id as business_id,t.flow_status,GET_DIC('FlowAuditStatus',t.flow_status) as flow_status_name,t.create_time,t.update_time,t.update_time as deal_time,t.primitive_id";
		StringBuilder sb = new StringBuilder(sql);
		String[] tableNames = new String[] {"t_acade_achive_info","t_patent_info","t_proj_achieve_info","t_stand_info","t_tec_innov_info","t_tec_man_innov"};
		
		sb.append(" from ")
		.append(" (SELECT * FROM (")
		.append(" SELECT distinct id,primitive_id,create_time,update_time")
		.append(" FROM ")
		.append(process.getTabName())
		.append(" ORDER BY create_time DESC,update_time DESC) m")
		.append(" GROUP BY m.primitive_id")
		.append(" ORDER BY m.create_time DESC,m.update_time DESC) temp")
		.append(" LEFT JOIN ")
		.append(process.getTabName())
		.append("  t ON t.id=temp.id");
		
		if(Arrays.asList(tableNames).contains(process.getTabName().toLowerCase())) {//判断是否多表
			sb.append(" left join t_part_rank pr on t.id=pr.item_id");
		}
		
		sb.append(" where 1=1")
		.append(" and t.DEL_SIGN=0");
		
		List<Object> paramList = new ArrayList<Object>();
		if(null != process) {
			if((null != process.getFlowStatus())&&(-1 != process.getFlowStatus())){
	        	sb.append(" and t.flow_status = ? ");
	        	paramList.add(process.getFlowStatus());
	        }
	        if(StringUtils.isNotBlank(process.getDealErpNo())){
	        	if(Arrays.asList(tableNames).contains(process.getTabName().toLowerCase())) {//判断是否多表
	        		sb.append(" and pr.erp_no = ? ");
				}else {//非多表
					sb.append(" and t.erp_no = ? ");
				}
	        	paramList.add(process.getDealErpNo());
	        }
	        if(null != process.getStartTime()){
	        	sb.append(" and t.create_time >= ? ");
	        	paramList.add(DateUtils.getStartTimeOfDay(process.getStartTime()));
	        }
	        if(null != process.getEndTime()){
	        	sb.append(" and t.create_time <= ? ");
	        	paramList.add(DateUtils.getEndTimeOfDay(process.getEndTime()));
	        }
		}
		sb.append(" order by t.create_time desc ");
        
        this.findPageBySql(ProcessListVo.class, sb.toString(), page, paramList.toArray());
	}
	

	/**
	 * 更新业务流程状态
	 * @author yangwang
	 * @param businessDto
	 *  */
	@Override
	public void updateBusinessWorkFlowStatus(BusinessWorkFlowDto businessDto) {
		String sql = "UPDATE "; 
		StringBuilder sb = new StringBuilder(sql);
		
		sb.append(businessDto.getTabName())
		.append(" set ")
		.append(" flow_status=#{flow_status,jdbcType=INTEGER},")
		.append(" updater_id=#{updater_id,jdbcType=VARCHAR},")
		.append(" updater_name=#{updater_name,jdbcType=VARCHAR},")
		.append(" update_time=#{update_time}");
		if((null != businessDto)&&(1 == businessDto.getFolwStatus())) {
			sb.append(",is_valid=0");
		}
		sb.append(" where id=#{id,jdbcType=VARCHAR} ");
		
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("flow_status", businessDto.getFolwStatus());
		params.put("updater_id", businessDto.getErpNo());
		params.put("updater_name", businessDto.getUserName());
		params.put("update_time", businessDto.getDealTime());
		params.put("id", businessDto.getBusinessId());
		
		this.executeSql(sb.toString(), params);
	}
	
	/**
	 * 更新业务是否有效数据
	 * @author yangwang
	 * @param businessDto
	 *  */
	@Override
	public void updateBusinessIsValid(BusinessWorkFlowDto businessDto) {
		if((null != businessDto)&&(1 == businessDto.getFolwStatus())) {
			String sql = "UPDATE "; 
			StringBuilder sb = new StringBuilder(sql);
			
			sb.append(businessDto.getTabName())
			.append(" set ")
			.append(" is_valid=1,")
			.append(" updater_id=#{updater_id,jdbcType=VARCHAR},")
			.append(" updater_name=#{updater_name,jdbcType=VARCHAR},")
			.append(" update_time=#{update_time}")
			.append(" where id=(")
			.append(" select temp.orignal_id")
			.append(" from (select orignal_id")
			.append(" from ")
			.append(businessDto.getTabName())
			.append(" where id=#{id,jdbcType=VARCHAR}) temp)");
			
			Map<String, Object> params = new HashMap<String, Object>();
			params.put("flow_status", businessDto.getFolwStatus());
			params.put("updater_id", businessDto.getErpNo());
			params.put("updater_name", businessDto.getUserName());
			params.put("update_time", businessDto.getDealTime());
			params.put("id", businessDto.getBusinessId());
			
			this.executeSql(sb.toString(), params);
		}
	}
	
	/**
	 * 获取业务流程统计
	 * @author yangwang
	 * @param tableName 表名
	 * @param erpNo erp编码
	 * @param businessDto
	 *  */
	@Override
	public WorkFlowCount getBusinessFlowCount(String tableName,ProcessListVo process) {
		String sql = "select COUNT(t.id) as flow_count";
		StringBuilder sb = new StringBuilder(sql);
		List<Object> paramList = new ArrayList<Object>();
		String[] tableNames = new String[] {"t_acade_achive_info","t_patent_info","t_proj_achieve_info","t_stand_info","t_tec_innov_info","t_tec_man_innov"};
		
		sb.append(" from ")
		.append(" (SELECT * FROM (")
		.append(" SELECT distinct id,primitive_id,create_time,update_time")
		.append(" FROM ")
		.append(tableName)
		.append(" ORDER BY create_time DESC,update_time DESC) m")
		.append(" GROUP BY m.primitive_id")
		.append(" ORDER BY m.create_time DESC,m.update_time DESC) temp")
		.append(" LEFT JOIN ")
		.append(tableName)
		.append("  t ON t.id=temp.id");

		if(Arrays.asList(tableNames).contains(tableName.toLowerCase())) {//判断是否多表
			sb.append(" left join t_part_rank pr on t.id=pr.item_id");
		}
		
		sb.append(" where t.DEL_SIGN=0");
		
		if(Arrays.asList(tableNames).contains(tableName.toLowerCase())) {//判断是否多表
    		sb.append(" and pr.erp_no = ? ");
		}else {//非多表
			sb.append(" and t.erp_no = ? ");
		}
    	paramList.add(process.getDealErpNo());
		
		if((null != process.getFlowStatus())&&(-1 != process.getFlowStatus())) {
			sb.append(" and t.flow_status=?");
			paramList.add(process.getFlowStatus());
		}
		if(null != process.getStartTime()){
        	sb.append(" and t.create_time >= ? ");
        	paramList.add(DateUtils.getStartTimeOfDay(process.getStartTime()).getTime());
        }
        if(null != process.getEndTime()){
        	sb.append(" and t.create_time <= ? ");
        	paramList.add(DateUtils.getEndTimeOfDay(process.getEndTime()).getTime());
        }
		
		return this.findUnique(WorkFlowCount.class, sb.toString(), paramList.toArray());
	}
	
	/**
	 * 获取字典列表
	 * @param value
	 * @return List<TSysDictionaryInfo>
	 *  */
	@Override
	public List<TSysDictionaryInfo> getDictionarys(String value){
		String sql = "select id,name,value from t_sys_dictionary_info ";
    	StringBuilder sb = new StringBuilder(sql);
    	sb.append(" where del_sign=0")
    	.append("  and p_id=(select id")
    	.append("  from t_sys_dictionary_info")
    	.append(" where del_sign=0")
    	.append(" and value=?)");
    	
    	return this.findAllBySQL(TSysDictionaryInfo.class, sb.toString(), null, false, new Object[] {value});
	}
	
	/**
	 * 删除业务
	 * @param user 统一权限用户
	 * @param process
	 *  */
	@Override
	public void deleteBusiness(User user,ProcessListVo process) {
		String sql = "UPDATE "; 
		StringBuilder sb = new StringBuilder(sql);
		
		
		sb.append(process.getTabName())
		.append(" set ")
		.append(" del_sign=1,")
		.append(" updater_id=#{updater_id,jdbcType=VARCHAR},")
		.append(" updater_name=#{updater_name,jdbcType=VARCHAR},")
		.append(" update_time=#{update_time}");
		sb.append(" where id=#{id,jdbcType=VARCHAR} ");
		
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("updater_id", user.getFax());
		params.put("updater_name", user.getName());
		params.put("update_time", new Date());
		params.put("id", process.getBusinessId());
		
		this.executeSql(sb.toString(), params);
	}
	
}
